from flask import jsonify, request
from app.api import jiaoyu
from app.setting import DevConfig

engine = DevConfig.engine


# 注册教育大数据api的蓝图


@jiaoyu.route('/')
def hello_word():
    return "<h1>hello_word</h1>"


'''
教育大数据项目(/api/jiaoyu/)
'''


@jiaoyu.route('/studentNum4Country', methods=["GET"])
def chinaCountry():
    # 全国各省学生人数分布
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        print("拿到的年为", year)
        # year分为2018和2019
        sql = 'select chinaProvince as name,count(*)as value,sum(case when sex="男" then  1 else 0 end) as boy,sum(case when sex="女" then  1 else 0 end) as girl from studentdata where year="{}" and chinaProvince!="" group by chinaProvince;'.format(year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            boy = str(data[2])
            girl = str(data[3])
            jsondata = {
                "value": value,
                "name": name,
                "boy": boy,
                "girl": girl
            }
            app.append(jsondata)
        return jsonify(app)


@jiaoyu.route('/studentNum4Guangdong', methods=["GET"])
def guangdongCity():
    # 广东省各地级市学生人数分布
    # 广东省各地级市学生人数分布
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        print("拿到的年为", year)
        # year分为2018和2019
        sql = 'select  guangdongProvince as name,count(*)as value,sum(case when sex="男" then  1 else 0 end) as boy,sum(case when sex="女" then  1 else 0 end) as girl from studentdata where year="{}" and chinaProvince="广东" group by guangdongProvince ;'.format(year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            boy = str(data[2])
            girl = str(data[3])
            jsondata = {
                "value": value,
                "name": name,
                "boy": boy,
                "girl": girl
            }
            app.append(jsondata)
        return jsonify(app)


@jiaoyu.route('/studentSex', methods=["GET"])
def studentSex():
    # 考生性别分布
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = 'select sex, count(*) as count_sex from studentdata where year="{}" and sex!="" group by sex;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            value = data[1]
            jsondata = {
                "value": value,
                "name": name
            }
            app.append(jsondata)
        return jsonify(app)


@jiaoyu.route('/studentType', methods=["GET"])
def studentType():
    # 考生类别
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = 'select studentType, count(*) as count_sex from studentdata where year="{}" and studentType!="" group by studentType;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            name = data[0]
            if name == "农村应届" or name == "农村往届" or name == "城镇应届" or name == "城镇往届":
                value = data[1]
                jsondata = {
                    "value": value,
                    "name": name
                }
                app.append(jsondata)
            else:
                pass
        print(app)
        return jsonify(app)


@jiaoyu.route('/recruitType', methods=["GET"])
def srecruitType():
    # 招生类别
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = ' select recruitType, count(*) as count_sex from studentdata where year="{}" and recruitType!="" group by recruitType;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            type = data[0]
            num = data[1]
            jsondata = {
                "num": num,
                "type": type
            }
            app.append(jsondata)
        return jsonify(app)


@jiaoyu.route('/studentNum4Institute', methods=["GET"])
def studentNum4Institute():
    # 各学院新生入学人数
    if request.method == 'GET':
        formdata = request.args
        year = formdata['year']
        sql = ' select college,count(*) as count_college from studentdata where year="{}" and college!="" group by college;'.format(
            year)
        datas = engine.execute(sql)
        app = []
        for data in datas:
            type = data[0]
            num = data[1]
            jsondata = {
                "type": type,
                "num": num
            }
            app.append(jsondata)
        return jsonify(app)


@jiaoyu.route('/grade_1')
def grade1():
    # 2018-2019 第一学期成绩
    sql = 'select avg(web_design),avg(applied_mathematics_1),avg(fundamentals_of_programming),avg(data_base) from achievementdata ;'
    datas = engine.execute(sql)
    app = []
    for data in datas:
        jsondata = {
            "网页设计基础": int(data[0]),
            "应用数学": int(data[1]),
            "程序设计基础": int(data[2]),
            "数据应用基础": int(data[3])
        }
        app.append(jsondata)
    return jsonify(app)


@jiaoyu.route('/grade_2')
def grade2():
    sql = 'select avg(applied_mathematics_2),avg(linux_system),avg(python_program),avg(hadoop_data) from achievementdata ;'
    datas = engine.execute(sql)
    app = []
    for data in datas:
        jsondata = {
            "应用数学（2）": int(data[0]),
            "Linux 网络操作系统": int(data[1]),
            "Python 程序设计": int(data[2]),
            "Hadoop 大数据技术": int(data[3])
        }
        app.append(jsondata)
    return jsonify(app)


@jiaoyu.route('/bookStudent')
def bookStudent():
    # 有借阅行为的学生人数占比
    book_some=[{
        "value":"3841",
        "name":"未借阅图书人数"
    },
        { "value":"19371",
        "name":"已借阅图书人数"}
    ]
    return jsonify(book_some)


@jiaoyu.route('/bookLib')
def bookLib():
    # 各类别图书借阅数据（分图书馆）
    sql = ' select  book_hold,count(*) as counts  from bookdata group by book_hold;'
    datas = engine.execute(sql)
    app = []
    for data in datas:
        jsondata = {
            "name": data[0],
            "value":data[1],
        }
        app.append(jsondata)
    return jsonify(app)


# 请求参数 lib 代表图书馆索引（目前有 0-7 八个图书馆）
@jiaoyu.route('/bookLib/lib')
def bookLibIndex():
    # 各 图 书 馆 图 书 借 阅 数 量 （ 分 图 书 类 别 ）
    booklib=[
        {"name":"马克思主义、列宁主义、毛泽东思想、邓小平理论","value":"644"},
        {"name":"哲学、宗教","value":"510"},
        {"name":"政治、法律 ","value":"917"},
        {"name": "军事", "value": "616"},
        {"name": "经济", "value": "1492"},
        {"name": "文化、科学、教育、体育 ", "value": "1019"},
        {"name": "语言、文字", "value": "1261"},
        {"name": "文学", "value": "1930"},
        {"name": "艺术", "value": "877"},
        {"name": "历史、地理", "value": "981"},
        {"name": "数理科学和化学  ", "value": "591"},
        {"name": "自然科学总论", "value": "1885"},
        {"name": "天文学、地球科学", "value": "814"},
        {"name": "生物科学", "value": "619"},
        {"name": "农业科学", "value": "617"},
        {"name": "交通运输", "value": "494"},
        {"name": "航空、航天 ", "value": "756"},
        {"name": "环境科学、劳动保护科学(安全科学) ", "value": "610"},
        {"name": "综合性图书", "value": "710"},

    ]
    return jsonify(booklib)


@jiaoyu.route('/bookMajor')
def bookMajor():
    # 各专业借阅数
    sql = '  select professional,count(*) from bookdata group by professional;'
    datas = engine.execute(sql)
    app = []
    for data in datas:
        jsondata = {
            "name": data[0],
            "value": data[1],
        }
        app.append(jsondata)
    return jsonify(app)


@jiaoyu.route('/aptitude')
def aptitude():
    # 学生职业性向数据分析（2018 级大数据人工智能学院）
    sql = 'select avg(affair),avg(operation),avg(skill),avg(plan),avg(invention),avg(art),avg(humanity),avg(service),avg(communication),avg(influence),avg(extension),avg(control) from characterdata;'
    datas = engine.execute(sql)
    app = []
    for data in datas:
        jsondata = {
            "事务": int(data[0]),
            "操作": int(data[1]),
            "技能": int(data[2]),
            "规划": int(data[3]),
            "发明": int(data[4]),
            "艺术": int(data[5]),
            "人文": int(data[6]),
            "服务": int(data[7]),
            "交际": int(data[8]),
            "影响": int(data[9]),
            "开拓": int(data[10]),
            "管控": int(data[11])
        }
        app.append(jsondata)
    return jsonify(app)
